package com.yx.changdao.generate.utils;

/**
 * @Description: <br/>
 * 数据库相关
 * <p>
 * <br/>
 * @Auther: zhangqi
 * @create 2019/11/22 13:56
 */

import com.yx.changdao.generate.model.DataDictClass;
import com.yx.changdao.generate.model.DataDictParam;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 读取mysql某数据库下表的注释信息
 *
 * @author xxx
 */
public class MySQLTableComment {

    /**
     * 数据库连接驱动
     */
    private static final String DRIVER = "com.mysql.jdbc.Driver";

    static Statement stmt = null;

    static ResultSet rs = null;

    static Connection conn = null;

    public static Connection getMySQLConnection(String URL, String username, String password) throws Exception {
        Class.forName(DRIVER);
        conn = DriverManager.getConnection(URL, username, password);
        return conn;
    }


    /**
     * 获取当前数据库下的所有表名称
     *
     * @return
     * @throws Exception
     */
    public static List getAllTableName(Connection conn) throws Exception {
        List tables = new ArrayList();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SHOW TABLES ");
        while (rs.next()) {
            String tableName = rs.getString(1);
            tables.add(tableName);
        }
        return tables;
    }

    /**
     * 获取数据字典
     *
     * @param conn
     * @return
     * @throws Exception
     */
    public static List<Map<String, List<DataDictClass>>> getDataDict(Connection conn) throws Exception {
        List<Map<String, List<DataDictClass>>> list = new ArrayList<>();

        Statement stmt = conn.createStatement();

        List allTableName = getAllTableName(conn);
        for (Object o : allTableName) {
            Map<String, List<DataDictClass>> map = new HashMap<>();

            String tableName = o.toString();
            ResultSet rs = stmt.executeQuery("select " +
                    "COLUMN_NAME filed_name," +
                    "COLUMN_TYPE filed_type," +
                    "COLUMN_COMMENT filed_content, " +
                    "COLUMN_KEY filed_ispri " +
                    "from information_schema.columns " +
                    "where TABLE_SCHEMA='" + DataDictParam.DB_NAME + "' AND  TABLE_NAME='" + tableName + "';");

            List<DataDictClass> fieldList = new ArrayList<>();
            while (rs.next()) {
                DataDictClass data = new DataDictClass();

                String filedIspri = rs.getString("filed_ispri").replaceAll("\\s","");

                data.setFiledIspri(filedIspri.equals("PRI") ? "是" : "");
                data.setTableName(tableName);
                data.setFiledContent(rs.getString("filed_content").replaceAll("\\s",""));
                data.setFiledName(rs.getString("filed_name").replaceAll("\\s",""));
                data.setFiledType(rs.getString("filed_type").replaceAll("\\s",""));

                fieldList.add(data);
            }
            map.put(tableName, fieldList);
            list.add(map);
        }

        close();
        return list;
    }

//    /**
//     * 获取数据字典
//     *
//     * @param conn
//     * @return
//     * @throws Exception
//     */
//    public static List<Map<String, DataDictClassList>> getDataDict(Connection conn) throws Exception {
//        List<Map<String, DataDictClassList>> list = new ArrayList<>();
//
//        Statement stmt = conn.createStatement();
//
//        List allTableName = getAllTableName(conn);
//        for (Object o : allTableName) {
//            Map<String, DataDictClassList> map = new HashMap<>();
//
//            String tableName = o.toString();
//            ResultSet rs = stmt.executeQuery("select " +
//                    "COLUMN_NAME filed_name," +
//                    "COLUMN_TYPE filed_type," +
//                    "COLUMN_COMMENT filed_content, " +
//                    "COLUMN_KEY filed_ispri " +
//                    "from information_schema.columns " +
//                    "where TABLE_SCHEMA='" + DataDictParam.DB_NAME + "' AND  TABLE_NAME='" + tableName + "';");
//
//            List<DataDictClass> fieldList = new ArrayList<>();
//            DataDictClassList dataDictClassList = new DataDictClassList();
//            while (rs.next()) {
//                DataDictClass data = new DataDictClass();
//
//                String filedIspri = rs.getString("filed_ispri");
//
//                data.setFiledIspri(filedIspri.equals("PRI") ? "是" : "fou");
//                data.setTableName(tableName);
//                data.setFiledContent(rs.getString("filed_content"));
//                data.setFiledName(rs.getString("filed_name"));
//                data.setFiledType(rs.getString("filed_type"));
//
//                fieldList.add(data);
//
//            }
//            dataDictClassList.setList(fieldList);
//            map.put(tableName, dataDictClassList);
//            list.add(map);
//        }
//
//        close();
//        return list;
//    }


    /**
     * 获得某表的建表语句
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public static Map getCommentByTableName(List tableName, Connection conn) throws Exception {
        Map map = new HashMap();
        Statement stmt = conn.createStatement();
        for (int i = 0; i < tableName.size(); i++) {
            String table = (String) tableName.get(i);
            ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + table);
            if (rs != null && rs.next()) {
                String createDDL = rs.getString(2);
                String comment = parse(createDDL);
                map.put(table, comment);
            }
        }
        return map;
    }


    /**
     * 返回注释信息
     *
     * @param all
     * @return
     */

    public static String parse(String all) {
        String comment = null;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }

    /**
     * 关闭数据库连接
     */
    public static void close() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
